package com.interview.productsalesreports.repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

@Repository
public class ProductSalesReportsRepository {
	@PersistenceContext
	private EntityManager entityManager;

	// 某一时刻的报表 date to string yyyy-MM

	public List<Date> AllSalseOrderCreateTime() {
		String sql = "select * from "
				+ " (select  DISTINCT  sa.order_create_time as createTime "
				+ "from sales_order sa) saTime ";
		Session session = (Session) entityManager.getDelegate();

		Query query = session.createSQLQuery(sql);

		List<Date> result = query.list();

		return result;
	}

	public List AllcreateTime() {
		String sql = "select  DATE_FORMAT(createTime,'%Y') as year,"
				+ "DATE_FORMAT(createTime,'%m') as mon " + " from " + "("
				+ "select sa.order_create_time as createTime" + " from "
				+ " sales_order sa " + " ) saTime group by year, mon; ";

		Session session = (Session) entityManager.getDelegate();
		Query query = session.createSQLQuery(sql);

		List result = query.setResultTransformer(
				Transformers.ALIAS_TO_ENTITY_MAP).list();

		return result;
	}

	public List productSalesValue(String year, String mon) {

		String years = year + "-" + mon + "%";
		String thatYear = year + "%";

		if (year == null) {
			years = "%-" + mon + "-%";
			thatYear = "%";
		}
		if (mon == null) {
			years = year + "%";
		}

		if (mon == null & year == null) {
			years = "%";
		}

		String sql = "select " + "name," + " cost, "
				+ " round(((cost/costsum)*100),2)monPercent , "
				+ " round((cost/sum)*100,2)yearPercent " + " from "
				+ " (select " + " * from " + " (select "
				+ " round(sum(sums),3)  sum " + " from " + " (select "
				+ " sum( DISTINCT  sum_cost)	as sums " + " from "
				+ " sales_order sa " + " inner join " + " order_list ord "
				+ " on " + " sa.id = ord.order_id " + " and "
				+ " sa.order_create_time like :thatYear " + " ) 	jk ) a " +

				" inner join " +

				" (select " + " sum(product_cost) as cost, "
				+ " product_name as name " + " from " + " sales_order sa "
				+ " inner join " + " order_list ord " + " on "
				+ " sa.id = ord.order_id " + " and "
				+ " sa.order_create_time like :years " + " group by name "
				+ " ) b "

				+ " inner join "

				+ " (select " + " sum(product_cost) costsum " + " from "
				+ " sales_order sa " + " inner join " + " order_list ord "
				+ " on " + " sa.id= ord.order_id" + " and "
				+ " sa.order_create_time like :years " + " ) c " + " ) d; ";

		Session session = (Session) entityManager.getDelegate();
		Query query = session.createSQLQuery(sql).setParameter("years", years)
				.setParameter("thatYear", thatYear);

		List result = query.setResultTransformer(
				Transformers.ALIAS_TO_ENTITY_MAP).list();

		return result;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<HashMap<String, Double>> monAmountAndYearAmount(String year,
			String mon) {
		String thatYear = year + "%";
		String thatMon = year + "-" + mon + "%";

		if (year == null & mon == null) {
			thatYear = "%";
			thatMon = "%";
		}
		if (year == null) {
			thatYear = "%";
			thatMon = "%" + mon + "%";
		}
		if (mon == null) {
			thatYear = "%";
			thatMon = year + "%";
		}
		String sql = " select * from " + " (select "
				+ " sum( DISTINCT  sum_cost)	as sums " + " from "
				+ " sales_order sa " + " inner join " + " order_list ord "
				+ " on " + " sa.id = ord.order_id " + " and "
				+ " sa.order_create_time like :thatYear ) year " +

				" inner join " +

				" ( " + " select " + " sum(product_cost) costsum " + " from "
				+ " sales_order sa " + " inner join " + " order_list ord "
				+ " on " + " sa.id= ord.order_id " + " and "
				+ " sa.order_create_time like :thatMon ) getsum ";

		Session session = (Session) entityManager.getDelegate();
		Query queryYear = session.createSQLQuery(sql)
				.setParameter("thatYear", thatYear)
				.setParameter("thatMon", thatMon);

		List<HashMap<String, Double>> sum = queryYear.setResultTransformer(
				Transformers.ALIAS_TO_ENTITY_MAP).list();

		return sum;
	}

	
}
